﻿Imports System.Data.SqlClient
Public Class frmREmpleadosTrans

    Private Sub frmREmpleadosTrans_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        radioAltas.Checked = True
        datBaja1.Value = Now()
        datBaja2.Value = Now()
        datAlta1.Value = Now()
        datAlta2.Value = Now()

    End Sub
    Sub altas()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim strFecha1 As String = Format(Me.datAlta1.Value.Year, "0000") & Format(Me.datAlta1.Value.Month, "00") & Format(Me.datAlta1.Value.Day, "00")
        Dim strFecha2 As String = Format(Me.datAlta2.Value.Year, "0000") & Format(Me.datAlta2.Value.Month, "00") & Format(Me.datAlta2.Value.Day, "00")

        Dim strSql As String = ""

        strSql = "select E.IDEmpleado,Nombrecompleto,direccion,EstadoCivil,Licencia,L.Tipo,Vigencia,FechaAlta"
        strSql = strSql & " from Empleados E INNER JOIN Licencias L"
        strSql = strSql & " ON E.IDEmpleado=L.IDEmpleado"
        strSql = strSql & " order by E.IdEmpleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader




        wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Operador"
        wb.SHEETS(1).cells(rng, 3).value = "Domicilio"
        wb.SHEETS(1).cells(rng, 4).value = "Colonia"
        wb.SHEETS(1).cells(rng, 5).value = "No. de Licencia"
        wb.SHEETS(1).cells(rng, 6).value = "Vigencia"
        wb.SHEETS(1).cells(rng, 7).value = "Tipo"
        wb.SHEETS(1).cells(rng, 8).value = "Estado Civil"
        Dim i As Integer
        For i = 1 To 8
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        Dim Civil As String = ""
        Dim cont As Integer = 0
        Dim str() As String
        Dim domicilio As String
        Dim colonia As String
        Dim Vigencia As Date
        Dim strFechaMov As String = ""
        Dim tar As String = ""


        rng = rng + 1

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("FechaAlta").ToString).Year, "0000") & Format(CDate(rdBuscar("FechaAlta").ToString).Month, "00") & Format(CDate(rdBuscar("FechaAlta").ToString).Day, "00")
            'auxiliar = rdBuscar("Licencia").ToString.Trim
            'If auxiliar <> "" Then
            If strFechaMov >= strFecha1 And strFechaMov <= strFecha2 Then
                cont = cont + 1
                Civil = ""
                domicilio = ""
                colonia = ""
                tar = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "IDEmpleado=" & rdBuscar("idempleado") & " and status='ALTA'")
                If tar = "" Then
                    tar = "Sin Tarjeton"
                End If
                wb.SHEETS(1).cells(rng, 1).value = tar.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
                Try
                    str = Split(rdBuscar("Direccion"), ",", 3)
                    wb.SHEETS(1).cells(rng, 3).value = str(0) & " No. " & str(1)
                    wb.SHEETS(1).cells(rng, 4).value = str(2)
                Catch ex As Exception
                End Try
                str = Nothing
                wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Licencia").ToString.Trim
                Vigencia = rdBuscar("Vigencia")
                wb.SHEETS(1).cells(rng, 6).value = Vigencia.Day & "/" & Vigencia.Month & "/" & Vigencia.Year
                wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Tipo").ToString.Trim
                If rdBuscar("EstadoCivil").ToString.Trim = "C" Then
                    Civil = "Casado"
                ElseIf rdBuscar("EstadoCivil").ToString.Trim = "S" Then
                    Civil = "Soltero"
                ElseIf rdBuscar("EstadoCivil").ToString.Trim = "D" Then
                    Civil = "Divorciado"
                ElseIf rdBuscar("EstadoCivil").ToString.Trim = "V" Then
                    Civil = "Viudo"
                End If

                wb.SHEETS(1).cells(rng, 8).value = Civil
                For i = 1 To 8
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
            'End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 5
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 12


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Operadores Activos. Filtro: Fecha de Alta:  Del " & Me.datAlta1.Value.Day & "/" & Me.datAlta1.Value.Month & "/" & Me.datAlta1.Value.Year & " al " & Me.datAlta2.Value.Day & "/" & Me.datAlta2.Value.Month & "/" & Me.datAlta2.Value.Year
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Sub bajas()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim strFecha1 As String = Format(Me.datBaja1.Value.Year, "0000") & Format(Me.datBaja1.Value.Month, "00") & Format(Me.datBaja1.Value.Day, "00")
        Dim strFecha2 As String = Format(Me.datBaja2.Value.Year, "0000") & Format(Me.datBaja2.Value.Month, "00") & Format(Me.datBaja2.Value.Day, "00")

        Dim strSql As String = ""

        strSql = "select B.IDEmpleado,NombreCompleto,B.FechaBaja,B.CausaBaja"
        strSql = strSql & " from Empleados E INNER JOIN Bajas B  "
        strSql = strSql & " ON B.idempleado=E.idempleado"
        strSql = strSql & " order by B.fechabaja"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim tarjeton As String = ""


        wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Operador"
        wb.SHEETS(1).cells(rng, 3).value = "Fecha Baja"
        wb.SHEETS(1).cells(rng, 4).value = "Motivo de la Baja"
        Dim i As Integer
        For i = 1 To 4

            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        Dim cont As Integer = 0
        Dim strFechaMov As String = ""
        Dim baja As Date


        rng = rng + 1

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("FechaBaja").ToString).Year, "0000") & Format(CDate(rdBuscar("FechaBaja").ToString).Month, "00") & Format(CDate(rdBuscar("FechaBaja").ToString).Day, "00")
            If strFechaMov >= strFecha1 And strFechaMov <= strFecha2 Then
                cont = cont + 1
                tarjeton = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "IDEmpleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 1).value = tarjeton
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
                baja = rdBuscar("FechaBaja")
                wb.SHEETS(1).cells(rng, 3).value = baja.Day & "/" & baja.Month & "/" & baja.Year
                wb.SHEETS(1).cells(rng, 4).value = rdBuscar("causabaja").ToString.Trim
                For i = 1 To 4
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If

        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 60

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Operadores Inactivos. Filtro: Fecha de Baja:  Del " & datBaja1.Value.Day & "/" & datBaja1.Value.Month & "/" & datBaja1.Value.Year & " al " & datBaja2.Value.Day & "/" & datBaja2.Value.Month & "/" & datBaja2.Value.Year
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub radioAltas_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioAltas.CheckedChanged
        If radioAltas.Checked = True Then
            datAlta1.Enabled = True
            datAlta2.Enabled = True
            datBaja1.Enabled = False
            datBaja2.Enabled = False
        Else
            datAlta1.Enabled = False
            datAlta2.Enabled = False
            datBaja1.Enabled = True
            datBaja2.Enabled = True
        End If
    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        If radioAltas.Checked = True Then
            altas()
        Else
            bajas()
        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class